using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace SiteLib.Data
{
	[DataObject]
	[Serializable]
	public partial class SiteUser
	{
		#region Constants

		private const string TableName = "[dbo].[SiteUser]";

		#endregion

		#region Properties

		public int? UserId { get; set; }
		public string UserName
		{
			get { return Email; }
			private set { Email = value; }
		}
		public string Email { get; set; }
		public string Password { get; set; }
		public string PasswordSalt { get; set; }
		public string Comments { get; set; }
		public DateTime? CreatedDate { get; set; }
		public DateTime? LastModifiedDate { get; set; }
		public DateTime? LastLoginDate { get; set; }
		public string LastLoginIp { get; set; }
		public bool? IsActivated { get; set; }
		public bool? IsLockedOut { get; set; }
		public bool? IsDeleted { get; set; }
		public DateTime? LastLockedOutDate { get; set; }
		public string LastLockedOutReason { get; set; }
		public string NewPasswordKey { get; set; }
		public DateTime? NewPasswordRequested { get; set; }
		public string NewEmail { get; set; }
		public string NewEmailKey { get; set; }
		public DateTime? NewEmailRequested { get; set; }

		#endregion

		#region Methods

		#region Mapping Methods

		protected void MapTo(DataSet ds)
		{
			if (ds == null)
				ds = new DataSet();

			if (ds.Tables[TableName] == null)
				ds.Tables.Add(TableName);

			ds.Tables[TableName].Columns.Add("UserId", typeof(Int32));
			ds.Tables[TableName].Columns.Add("UserName", typeof(String));
			ds.Tables[TableName].Columns.Add("Email", typeof(String));
			ds.Tables[TableName].Columns.Add("Password", typeof(String));
			ds.Tables[TableName].Columns.Add("PasswordSalt", typeof(String));
			ds.Tables[TableName].Columns.Add("Comments", typeof(String));
			ds.Tables[TableName].Columns.Add("CreatedDate", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("LastModifiedDate", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("LastLoginDate", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("LastLoginIp", typeof(String));
			ds.Tables[TableName].Columns.Add("IsActivated", typeof(Boolean));
			ds.Tables[TableName].Columns.Add("IsLockedOut", typeof(Boolean));
			ds.Tables[TableName].Columns.Add("IsDeleted", typeof(Boolean));
			ds.Tables[TableName].Columns.Add("LastLockedOutDate", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("LastLockedOutReason", typeof(String));
			ds.Tables[TableName].Columns.Add("NewPasswordKey", typeof(String));
			ds.Tables[TableName].Columns.Add("NewPasswordRequested", typeof(DateTime));
			ds.Tables[TableName].Columns.Add("NewEmail", typeof(String));
			ds.Tables[TableName].Columns.Add("NewEmailKey", typeof(String));
			ds.Tables[TableName].Columns.Add("NewEmailRequested", typeof(DateTime));

			DataRow dr = ds.Tables[TableName].NewRow();

			dr["UserId"] = UserId ?? (object) DBNull.Value;
			dr["UserName"] = Email ?? (object) DBNull.Value;
			dr["Email"] = Email ?? (object) DBNull.Value;
			dr["Password"] = Password ?? (object) DBNull.Value;
			dr["PasswordSalt"] = PasswordSalt ?? (object) DBNull.Value;
			dr["Comments"] = Comments ?? (object) DBNull.Value;
			dr["CreatedDate"] = CreatedDate ?? (object) DBNull.Value;
			dr["LastModifiedDate"] = LastModifiedDate ?? (object) DBNull.Value;
			dr["LastLoginDate"] = LastLoginDate ?? (object) DBNull.Value;
			dr["LastLoginIp"] = LastLoginIp ?? (object) DBNull.Value;
			dr["IsActivated"] = IsActivated ?? (object) DBNull.Value;
			dr["IsLockedOut"] = IsLockedOut ?? (object) DBNull.Value;
			dr["IsDeleted"] = IsDeleted ?? (object)DBNull.Value;
			dr["LastLockedOutDate"] = LastLockedOutDate ?? (object) DBNull.Value;
			dr["LastLockedOutReason"] = LastLockedOutReason ?? (object) DBNull.Value;
			dr["NewPasswordKey"] = NewPasswordKey ?? (object) DBNull.Value;
			dr["NewPasswordRequested"] = NewPasswordRequested ?? (object) DBNull.Value;
			dr["NewEmail"] = NewEmail ?? (object) DBNull.Value;
			dr["NewEmailKey"] = NewEmailKey ?? (object) DBNull.Value;
			dr["NewEmailRequested"] = NewEmailRequested ?? (object) DBNull.Value;

			ds.Tables[TableName].Rows.Add(dr);
		}

		protected void MapFrom(DataRow dr)
		{
			UserId = dr["UserId"] != DBNull.Value ? Convert.ToInt32(dr["UserId"]) : UserId = null;
			UserName = dr["UserName"] != DBNull.Value ? Convert.ToString(dr["UserName"]) : UserName = null;
			Email = dr["Email"] != DBNull.Value ? Convert.ToString(dr["Email"]) : Email = null;
			Password = dr["Password"] != DBNull.Value ? Convert.ToString(dr["Password"]) : Password = null;
			PasswordSalt = dr["PasswordSalt"] != DBNull.Value ? Convert.ToString(dr["PasswordSalt"]) : PasswordSalt = null;
			Comments = dr["Comments"] != DBNull.Value ? Convert.ToString(dr["Comments"]) : Comments = null;
			CreatedDate = dr["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(dr["CreatedDate"]) : CreatedDate = null;
			LastModifiedDate = dr["LastModifiedDate"] != DBNull.Value ? Convert.ToDateTime(dr["LastModifiedDate"]) : LastModifiedDate = null;
			LastLoginDate = dr["LastLoginDate"] != DBNull.Value ? Convert.ToDateTime(dr["LastLoginDate"]) : LastLoginDate = null;
			LastLoginIp = dr["LastLoginIp"] != DBNull.Value ? Convert.ToString(dr["LastLoginIp"]) : LastLoginIp = null;
			IsActivated = dr["IsActivated"] != DBNull.Value ? Convert.ToBoolean(dr["IsActivated"]) : IsActivated = null;
			IsLockedOut = dr["IsLockedOut"] != DBNull.Value ? Convert.ToBoolean(dr["IsLockedOut"]) : IsLockedOut = null;
			IsDeleted = dr["IsDeleted"] != DBNull.Value ? Convert.ToBoolean(dr["IsDeleted"]) : IsDeleted = null;
			LastLockedOutDate = dr["LastLockedOutDate"] != DBNull.Value ? Convert.ToDateTime(dr["LastLockedOutDate"]) : LastLockedOutDate = null;
			LastLockedOutReason = dr["LastLockedOutReason"] != DBNull.Value ? Convert.ToString(dr["LastLockedOutReason"]) : LastLockedOutReason = null;
			NewPasswordKey = dr["NewPasswordKey"] != DBNull.Value ? Convert.ToString(dr["NewPasswordKey"]) : NewPasswordKey = null;
			NewPasswordRequested = dr["NewPasswordRequested"] != DBNull.Value ? Convert.ToDateTime(dr["NewPasswordRequested"]) : NewPasswordRequested = null;
			NewEmail = dr["NewEmail"] != DBNull.Value ? Convert.ToString(dr["NewEmail"]) : NewEmail = null;
			NewEmailKey = dr["NewEmailKey"] != DBNull.Value ? Convert.ToString(dr["NewEmailKey"]) : NewEmailKey = null;
			NewEmailRequested = dr["NewEmailRequested"] != DBNull.Value ? Convert.ToDateTime(dr["NewEmailRequested"]) : NewEmailRequested = null;

			FillUserRoles();
		}

		public static List<SiteUser> MapFrom(DataSet ds)
		{
			// Initialise Collection.
			List<SiteUser> objects = new List<SiteUser>();

			// Validation.
			if (ds == null)
				throw new ApplicationException("Cannot map to dataset null.");
			
			if (ds.Tables[TableName].Rows.Count == 0)
				return objects;

			if (ds.Tables[TableName] == null)
				throw new ApplicationException("Cannot find table [dbo].[SiteUser] in DataSet.");

			if (ds.Tables[TableName].Rows.Count < 1)
				throw new ApplicationException("Table [dbo].[SiteUser] is empty.");

			// Map DataSet to Instance.
			foreach (DataRow dr in ds.Tables[TableName].Rows)
			{
				SiteUser instance = new SiteUser();
				instance.MapFrom(dr);
				instance.FillUserRoles();
				objects.Add(instance);
			}

			// Return collection.
			return objects;
		}

		#endregion

		#region CRUD Methods

		public static SiteUser Get(Int32 userId)
		{
			SiteUser instance = new SiteUser();

			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_SELECT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					if (ds == null || ds.Tables[0].Rows.Count == 0) throw new ApplicationException(String.Format("Could not get SiteUser ID:{0} from Database.", userId));
					ds.Tables[0].TableName = TableName;

					instance.MapFrom(ds.Tables[0].Rows[0]);
				}
			}
			return instance;
		}

		#region INSERT

		private void Insert(String userName, String email, String password, String passwordSalt, String comments, DateTime? createdDate, DateTime? lastModifiedDate, DateTime? lastLoginDate, String lastLoginIp, Boolean? isActivated, Boolean? isLockedOut, Boolean? isDeleted, DateTime? lastLockedOutDate, String lastLockedOutReason, String newPasswordKey, DateTime? newPasswordRequested, String newEmail, String newEmailKey, DateTime? newEmailRequested, DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_INSERT";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userName.ToLower(), email.ToLower(), password, passwordSalt, comments, createdDate, lastModifiedDate, lastLoginDate, lastLoginIp, isActivated, isLockedOut, isDeleted, lastLockedOutDate, lastLockedOutReason, newPasswordKey, newPasswordRequested, newEmail, newEmailKey, newEmailRequested))
			{
				UserId = Convert.ToInt32(transaction == null ? db.ExecuteScalar(dbCommand) : db.ExecuteScalar(dbCommand, transaction));
			}
		}

		/// <summary>
		/// Insert current SiteUser to database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Insert(DbTransaction transaction = null)
		{
			Insert(UserName, Email, Password, PasswordSalt, Comments, CreatedDate, LastModifiedDate, LastLoginDate, LastLoginIp, IsActivated, IsLockedOut, IsDeleted, LastLockedOutDate, LastLockedOutReason, NewPasswordKey, NewPasswordRequested, NewEmail, NewEmailKey, NewEmailRequested, transaction);
		}

		#endregion

		#region UPDATE

		private static void Update(Int32? userId, String userName, String email, String password, String passwordSalt, String comments, DateTime? createdDate, DateTime? lastModifiedDate, DateTime? lastLoginDate, String lastLoginIp, Boolean? isActivated, Boolean? isLockedOut, Boolean? isDeleted, DateTime? lastLockedOutDate, String lastLockedOutReason, String newPasswordKey, DateTime? newPasswordRequested, String newEmail, String newEmailKey, DateTime? newEmailRequested, DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);

				dbCommand.Parameters["@userId"].Value = userId;
				dbCommand.Parameters["@userName"].Value = userName.ToLower();
				dbCommand.Parameters["@email"].Value = email.ToLower();
				dbCommand.Parameters["@password"].Value = password;
				dbCommand.Parameters["@passwordSalt"].Value = passwordSalt;
				dbCommand.Parameters["@comments"].Value = comments;
				dbCommand.Parameters["@createdDate"].Value = createdDate;
				dbCommand.Parameters["@lastModifiedDate"].Value = lastModifiedDate;
				dbCommand.Parameters["@lastLoginDate"].Value = lastLoginDate;
				dbCommand.Parameters["@lastLoginIp"].Value = lastLoginIp;
				dbCommand.Parameters["@isActivated"].Value = isActivated;
				dbCommand.Parameters["@isLockedOut"].Value = isLockedOut;
				dbCommand.Parameters["@isDeleted"].Value = isDeleted;
				dbCommand.Parameters["@lastLockedOutDate"].Value = lastLockedOutDate;
				dbCommand.Parameters["@lastLockedOutReason"].Value = lastLockedOutReason;
				dbCommand.Parameters["@newPasswordKey"].Value = newPasswordKey;
				dbCommand.Parameters["@newPasswordRequested"].Value = newPasswordRequested;
				dbCommand.Parameters["@newEmail"].Value = newEmail;
				dbCommand.Parameters["@newEmailKey"].Value = newEmailKey;
				dbCommand.Parameters["@newEmailRequested"].Value = newEmailRequested;

				if (transaction == null)
					db.ExecuteNonQuery(dbCommand);
				else
					db.ExecuteNonQuery(dbCommand, transaction);
			}
		}

		private static void Update(Int32? userId, String userName, String email, String password, String passwordSalt, String comments, DateTime? createdDate, DateTime? lastModifiedDate, DateTime? lastLoginDate, String lastLoginIp, Boolean? isActivated, Boolean? isLockedOut, Boolean? isDeleted, DateTime? lastLockedOutDate, String lastLockedOutReason, String newPasswordKey, DateTime? newPasswordRequested, String newEmail, String newEmailKey, DateTime? newEmailRequested)
		{
			Update(userId, userName.ToLower(), email.ToLower(), password, passwordSalt, comments, createdDate, lastModifiedDate, lastLoginDate, lastLoginIp, isActivated, isLockedOut, isDeleted, lastLockedOutDate, lastLockedOutReason, newPasswordKey, newPasswordRequested, newEmail, newEmailKey, newEmailRequested, null);
		}

		private static void Update(SiteUser siteUser)
		{
			siteUser.Update();
		}

		private static void Update(SiteUser siteUser, DbTransaction transaction)
		{
			siteUser.Update(transaction);
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Update(DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_UPDATE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
			{
				db.DiscoverParameters(dbCommand);

				dbCommand.Parameters["@userId"].SourceColumn = "UserId";
				dbCommand.Parameters["@userName"].SourceColumn = "UserName";
				dbCommand.Parameters["@email"].SourceColumn = "Email";
				dbCommand.Parameters["@password"].SourceColumn = "Password";
				dbCommand.Parameters["@passwordSalt"].SourceColumn = "PasswordSalt";
				dbCommand.Parameters["@comments"].SourceColumn = "Comments";
				dbCommand.Parameters["@createdDate"].SourceColumn = "CreatedDate";
				dbCommand.Parameters["@lastModifiedDate"].SourceColumn = "LastModifiedDate";
				dbCommand.Parameters["@lastLoginDate"].SourceColumn = "LastLoginDate";
				dbCommand.Parameters["@lastLoginIp"].SourceColumn = "LastLoginIp";
				dbCommand.Parameters["@isActivated"].SourceColumn = "IsActivated";
				dbCommand.Parameters["@isLockedOut"].SourceColumn = "IsLockedOut";
				dbCommand.Parameters["@isDeleted"].SourceColumn = "IsDeleted";
				dbCommand.Parameters["@lastLockedOutDate"].SourceColumn = "LastLockedOutDate";
				dbCommand.Parameters["@lastLockedOutReason"].SourceColumn = "LastLockedOutReason";
				dbCommand.Parameters["@newPasswordKey"].SourceColumn = "NewPasswordKey";
				dbCommand.Parameters["@newPasswordRequested"].SourceColumn = "NewPasswordRequested";
				dbCommand.Parameters["@newEmail"].SourceColumn = "NewEmail";
				dbCommand.Parameters["@newEmailKey"].SourceColumn = "NewEmailKey";
				dbCommand.Parameters["@newEmailRequested"].SourceColumn = "NewEmailRequested";

				using (DataSet ds = new DataSet())
				{
					MapTo(ds);
					ds.AcceptChanges();
					ds.Tables[0].Rows[0].SetModified();

					if (transaction == null)
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, UpdateBehavior.Standard);
					else
						db.UpdateDataSet(ds, TableName, null, dbCommand, null, transaction);
				}
			}
		}

		/// <summary>
		/// Updates changes to the database.
		/// </summary>
		private void Update()
		{
			Update((DbTransaction)null);
		}

		#endregion

		#region DELETE

		private static void Delete(Int32? userId, DbTransaction transaction = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
		}

	    /// <summary>
		/// Delete current SiteUser from database.
		/// </summary>
		/// <param name="transaction">optional SQL Transaction</param>
		private void Delete(DbTransaction transaction)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_DELETE";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, UserId))
			{
				if (transaction != null)
				{
					db.ExecuteNonQuery(dbCommand, transaction);
				}
				else
				{
					db.ExecuteNonQuery(dbCommand);
				}
			}
			UserId = null;
		}

		/// <summary>
		/// Delete current SiteUser from database.
		/// </summary>
		private void Delete()
		{
			Delete((DbTransaction)null);
		}

		#endregion

		#region SEARCH
		
		//TODO: Research about .ToLower() fo email and username
		private static List<SiteUser> Search(Int32? userId = null, String userName = null, String email = null, String password = null, String passwordSalt = null, String comments = null, DateTime? createdDate = null, DateTime? lastModifiedDate = null, DateTime? lastLoginDate = null, String lastLoginIp = null, Boolean? isActivated = null, Boolean? isLockedOut = null, Boolean? isDeleted = null, DateTime? lastLockedOutDate = null, String lastLockedOutReason = null, String newPasswordKey = null, DateTime? newPasswordRequested = null, String newEmail = null, String newEmailKey = null, DateTime? newEmailRequested = null)
		{
			Database db = DatabaseFactory.CreateDatabase();
			const string sqlCommand = "[dbo].gspSiteUser_SEARCH";
			using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, userId, userName, email, password, passwordSalt, comments, createdDate, lastModifiedDate, lastLoginDate, lastLoginIp, isActivated, isLockedOut, isDeleted, lastLockedOutDate, lastLockedOutReason, newPasswordKey, newPasswordRequested, newEmail, newEmailKey, newEmailRequested))
			{
				using (DataSet ds = db.ExecuteDataSet(dbCommand))
				{
					ds.Tables[0].TableName = TableName;
					return MapFrom(ds);
				}
			}
		}

		private static List<SiteUser> Search(SiteUser searchObject)
		{
			return Search(searchObject.UserId, searchObject.UserName, searchObject.Email, searchObject.Password, searchObject.PasswordSalt, searchObject.Comments, searchObject.CreatedDate, searchObject.LastModifiedDate, searchObject.LastLoginDate, searchObject.LastLoginIp, searchObject.IsActivated, searchObject.IsLockedOut, searchObject.IsDeleted, searchObject.LastLockedOutDate, searchObject.LastLockedOutReason, searchObject.NewPasswordKey, searchObject.NewPasswordRequested, searchObject.NewEmail, searchObject.NewEmailKey, searchObject.NewEmailRequested);
		}

		#endregion

		#endregion

		#endregion
	}
}

